package com.ambow.teaching.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.ambow.teaching.entity.User;
import com.ambow.teaching.util.DBUtil;

/**
 * 用户数据访问对象。
 * 
 * @author deng.zhao
 *
 */
public class UserDAO {

	public int add(User user) throws SQLException {
		final String sql = "insert into Users(name, password, registrationTime) values (?, ?, ?)";
		return DBUtil.executeUpdate(sql, user.getName(), user.getPassword(), user.getRegistrationTime());
	}

	public int remove(int id) throws SQLException {
		final String sql = "delete from Users where id = ?";
		return DBUtil.executeUpdate(sql, id);
	}

	public int update(User user) throws SQLException {
		final String sql = "update Users set name = ?, password = ?, registrationTime = ? where id = ?";
		return DBUtil.executeUpdate(sql, user.getName(), user.getPassword(), user.getRegistrationTime(), user.getId());
	}

	public User get(int id) throws SQLException {
		final String sql = "select id, name, password, registrationTime from Users where userid = ?";
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			conn = DBUtil.getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			if (rs.next()) {
				User user = new User();
				user.setId(rs.getInt(1));
				user.setName(rs.getString(2));
				user.setPassword(rs.getString(2));
				user.setRegistrationTime(new Date(rs.getDate(4).getTime()));
				return user;
			}
			return null;
		} finally {
			DBUtil.closeConnection(conn, stmt, rs);
		}
	}

	public List<User> listAll() throws SQLException {
		List<User> users = new ArrayList<User>();
		String sql = "select id, name, password, registrationTime from Users";

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			conn = DBUtil.getConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getInt(1));
				user.setName(rs.getString(2));
				user.setPassword(rs.getString(2));
				user.setRegistrationTime(new java.util.Date(rs.getDate(4).getTime()));
				users.add(user);
			}
		} finally {
			DBUtil.closeConnection(conn, stmt, rs);
		}

		return users;

	}

	public static void main(String[] args) {
		// UserDAO userDao = new UserDAO();
		// List<User> users = null;

		// try {
		// users = userDao.listAll();
		// } catch (SQLException e) {
		// e.printStackTrace();
		// }
		// for (User user : users) {
		// System.out.println(user);
		// }

		// try {
		// userDao.add(new User(0, "Kitty", "snoopy", new java.util.Date()));
		// } catch (SQLException e) {
		// e.printStackTrace();
		// }

		// User user = userDao.get(3);
		// System.out.println(user);

		// int rowCount = userDao.remove(3);
		// System.out.println(rowCount);
	}

}
